package ru.spb.xmlreshelper.database;

import com.google.common.collect.Lists;
import ru.spb.xmlreshelper.domain.CDTRecord;

import java.io.*;
import java.sql.*;
import java.util.List;

/**
 * @author Nikita.Brazhnikov
 */
public class CDTDao {

    private Connection connection;

    private static final String SELECT_ALL = "SELECT ID, DESCRIPTION, NAMESPACE, SYSTEM_ID FROM CONTENT_DEF_TRANS";
    public static final String UPDATE_XSL = "UPDATE CONTENT_DEF_TRANS SET XSL_HTML = ? WHERE ID = ?";
    private static final String UPDATE_HTML = "UPDATE CONTENT_DEF_TRANS SET FORM_HTML = ? WHERE ID = ?";
    private static final String DOWNLOAD_HTML = "SELECT FORM_HTML FROM CONTENT_DEF_TRANS WHERE ID = ?";
    private static final String DOWNLOAD_XSL = "SELECT XSL_HTML FROM CONTENT_DEF_TRANS WHERE ID = ?";


    private Connection getConnection() throws SQLException {
        if (connection != null)
            return connection;
        return DatabaseManager.getInstance().getConnection();
    }

    public CDTDao(Connection connection) {
        this.connection = connection;
    }

    public List<CDTRecord> getCdts() throws SQLException {

        try (Connection con = getConnection();
             Statement statement = con.createStatement()) {
            ResultSet resultSet = statement.executeQuery(SELECT_ALL);
            List<CDTRecord> records = Lists.newLinkedList();
            while (resultSet.next()) {
                CDTRecord rec = new CDTRecord();
                rec.setCdt(resultSet.getLong("ID"));
                rec.setName(resultSet.getString("DESCRIPTION"));
                rec.setNamespace(resultSet.getString("NAMESPACE"));
                rec.setType(resultSet.getString("SYSTEM_ID"));
                records.add(rec);
            }
            return records;
        }
    }

    public void updateXsl(long cdtId, InputStream xslContent) throws SQLException {
       updateFile(cdtId, UPDATE_XSL, xslContent);
    }

    public void updateHtml(long cdtId, InputStream htmlContent) throws SQLException {
        updateFile(cdtId, UPDATE_HTML, htmlContent);
    }

    public InputStream downloadHtml(long cdtId) throws SQLException {
        return downloadFile(cdtId, DOWNLOAD_HTML, "FORM_HTML");
    }

    public InputStream downloadXsl(long cdtId) throws SQLException {
        return downloadFile(cdtId, DOWNLOAD_XSL, "XSL_HTML");
    }

    private InputStream downloadFile(long cdtId, String query, String col) throws SQLException {
        try (Connection con = getConnection(); PreparedStatement st = con.prepareStatement(query)) {
            st.setLong(1, cdtId);
            final ResultSet resultSet = st.executeQuery();
            Clob clob = resultSet.getClob(col);
            return clob.getAsciiStream();
        }
    }

    private void updateFile(Long cdtId, String updateQuery, InputStream content) throws SQLException {
        Connection con = getConnection();
        try (
             PreparedStatement st = con.prepareStatement(updateQuery);
             Reader reader = new InputStreamReader(content, "UTF-8")) {
            st.setClob(1, reader);
            st.setLong(2, cdtId);
            st.executeUpdate();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

}
